﻿
-- Create a new CLASS
IF NOT EXISTS ( SELECT 1 FROM dbo.UDS_Class WHERE ClassID = 301 )
BEGIN
    SET IDENTITY_INSERT dbo.UDS_Class ON;
    
    INSERT INTO dbo.UDS_Class ( ClassID, ClassName, ClassRemark, ClassType, ClassParentID, ClassAddedBy, ClassAddedDate, [Status] )
    VALUES(301, N'公共下载', N'公共下载', 301, 301, N'admin', getdate(), 0);
    
    SET IDENTITY_INSERT dbo.UDS_Class OFF;
END
GO

-- Create a new Permission type
IF NOT EXISTS ( SELECT 1 FROM dbo.UDS_Proc WHERE Proc_ID = 100 )
BEGIN
    SET IDENTITY_INSERT dbo.UDS_Proc ON;
    
    INSERT INTO dbo.UDS_Proc (Proc_ID, Proc_Name, Proc_Desc)
    VALUES(100, N'管理公共下载权', N'新增/更新/删除文件及文件分类');
    
    SET IDENTITY_INSERT dbo.UDS_Proc OFF;
END
IF NOT EXISTS ( SELECT 1 FROM dbo.UDS_Proc_Type WHERE Proc_ID = 100 AND Proc_Type = 301 )
BEGIN
    INSERT INTO dbo.UDS_Proc_Type(Proc_ID, Proc_Type) VALUES(100, 301);
END
IF NOT EXISTS ( SELECT 1 FROM dbo.UDS_Proc_Type WHERE Proc_ID = 1 AND Proc_Type = 301 )
BEGIN
    INSERT INTO dbo.UDS_Proc_Type(Proc_ID, Proc_Type) VALUES(1, 301);
END

-- Grant the administration permission for admin role
DECLARE @adminRole INT;
SELECT @adminRole = Role_ID FROM dbo.UDS_Role WHERE Role_Name = N'Admin';
IF ( @@ROWCOUNT <> 1 )
BEGIN
    RAISERROR(N'Cannot find "admin" role. Upgrade will be terminated!', 16, 1);
END;
IF NOT EXISTS ( SELECT 1 FROM dbo.UDS_Assign_Rule WHERE Act_ID = 100 AND Based_On = /* role */3 AND Role_ID = @adminRole )
BEGIN
    INSERT INTO dbo.UDS_Assign_Rule(Act_ID, Based_On, Role_ID) VALUES(100, 3, @adminRole);
END
GO

-- Grant browse permission for all roles
INSERT INTO dbo.UDS_Assign_Rule (Act_ID, Based_On, Team_ID, Role_ID)
SELECT /* browse permission */ 1, /* role-based */3, 301, Role_ID
FROM dbo.UDS_Role
WHERE Role_ID NOT IN (
	SELECT Role_ID
	FROM dbo.UDS_Assign_Rule
	WHERE Act_ID = 1 AND Based_On = 3 AND Team_ID = 301
);
GO


/*
 * Create table UDS_CF_Catalog
 */
IF ( OBJECT_ID(N'dbo.UDS_CF_Catalog', N'U') IS NULL )
BEGIN
    CREATE TABLE dbo.UDS_CF_Catalog
    (
        CatalogID       int             NOT NULL IDENTITY(1,1),
        Catalog         nvarchar(255)   COLLATE Chinese_PRC_CI_AS NOT NULL,
        Creator         nvarchar(50)    COLLATE Chinese_PRC_CI_AS NOT NULL,
        TimeCreated     datetime        NOT NULL CONSTRAINT [DF_UDS_CF_Catalog_TimeCreated] DEFAULT(getdate()),

        CONSTRAINT [PK_UDS_CF_Catalog] PRIMARY KEY CLUSTERED ( CatalogID asc )
    );

    CREATE UNIQUE INDEX [UNIQUE_UDS_CF_Catalog] ON dbo.UDS_CF_Catalog
    (
        Catalog asc
    );

    INSERT INTO dbo.UDS_CF_Catalog (Catalog, Creator) VALUES(N'普通DOC', N'admin');
    INSERT INTO dbo.UDS_CF_Catalog (Catalog, Creator) VALUES(N'SOP', N'admin');
    INSERT INTO dbo.UDS_CF_Catalog (Catalog, Creator) VALUES(N'FORM', N'admin');
    INSERT INTO dbo.UDS_CF_Catalog (Catalog, Creator) VALUES(N'其他１', N'admin');
    INSERT INTO dbo.UDS_CF_Catalog (Catalog, Creator) VALUES(N'其他２', N'admin');
END
GO

/*
 * Create table UDS_CF_File
 */
IF ( OBJECT_ID(N'dbo.UDS_CF_File', N'U') IS NULL )
BEGIN
    CREATE TABLE dbo.UDS_CF_File
    (
        FileID          int             NOT NULL IDENTITY(1,1),
        CatalogID       int             NOT NULL,
        [Name]          nvarchar(256)   COLLATE Chinese_PRC_CI_AS NOT NULL,
        [Version]       nvarchar(64)    COLLATE Chinese_PRC_CI_AS NOT NULL,
        [Size]          int             NOT NULL,
        VPath           nvarchar(512)   COLLATE Chinese_PRC_CI_AS NULL,
        Creator         nvarchar(50)    COLLATE Chinese_PRC_CI_AS NOT NULL,
        Invalid         bit             NOT NULL CONSTRAINT [DF_UDS_CF_File_Invalid] DEFAULT(0),
        TimeCreated     datetime        NOT NULL CONSTRAINT [DF_UDS_CF_File_TimeCreated] DEFAULT(getdate()),
        LastModified    datetime        NOT NULL CONSTRAINT [DF_UDS_CF_File_LastModified] DEFAULT(getdate()),

        CONSTRAINT [PK_UDS_CF_File] PRIMARY KEY NONCLUSTERED ( FileID asc ),
        CONSTRAINT [FK_UDS_CF_File_CatalogID] FOREIGN KEY (CatalogID) REFERENCES dbo.UDS_CF_Catalog(CatalogID)
    );

    CREATE CLUSTERED INDEX [IX_UDS_CF_File_Clustered] ON dbo.UDS_CF_File
    (
        CatalogID asc,
        LastModified desc
    );
END
GO
